﻿using datatablegenerator.Common;
using datatablegenerator.DB;
using datatablegenerator.Entity;
using datatablegenerator.Enums;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Windows;

namespace datatablegenerator.Factory.DBFactory.Provider
{
    public class SqlServerProvider : IDbContext
    {
        public DbConnection conn { get; set; }

        public Tuple<bool, string> CloseConnection()
        {
            try
            {
                conn.Close();
                if (conn.State != System.Data.ConnectionState.Closed)
                {
                    return Tuple.Create(true, "连接关闭失败！");
                }
            }
            catch (Exception ex)
            {
                return Tuple.Create(false, ex.Message);
            }
            return Tuple.Create(true, "连接已关闭！");
        }

        public Tuple<bool, string> ConnectionDb(string connectionStr)
        {
            try
            {
                DBHelper db = new DBHelper(connectionStr);
                var sqlConnection = db.Open();
                if (sqlConnection.State != System.Data.ConnectionState.Open)
                {
                    return Tuple.Create(false, "连接失败!");
                }

                ConfigurationManager.AppSettings.Set("MSSQL", $"{connectionStr}");
            }
            catch (Exception ex)
            {
                return Tuple.Create(false, ex.Message);
            }
            return Tuple.Create(true, "连接成功!");
        }

        public Tuple<bool, string> CreateDataTable(string sql, string dt, List<DatatableDTO> dto)
        {
            try
            {
                int tableExist = DBHelper.ExecuteNonQuery(sql, CommandType.Text);

                if (tableExist != -1)
                {
                    MessageBox.Show($"{dt}表,生成失败！", "提示", MessageBoxButton.OK, MessageBoxImage.Exclamation);
                    return Tuple.Create(false, $"{dt}表,生成失败！");
                }

                //execute sp_addextendedproperty 'MS_Description','字段备注信息','user','dbo','table','字段所属的表名','column','添加注释的字段名';
                string content = string.Empty;
                foreach (var item in dto)
                {
                    content += @$"execute sp_addextendedproperty 'MS_Description','{item.Remacks}','user','dbo','table','{dt}','column','{item.Name}';";
                }
                DBHelper.ExecuteNonQuery(content, CommandType.Text);
                MessageBox.Show($"{dt}表,生成成功！", "提示", MessageBoxButton.OK, MessageBoxImage.Asterisk);

                return Tuple.Create(true, $"{dt}表,生成失败！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "提示", MessageBoxButton.OK, MessageBoxImage.Error);
                return Tuple.Create(false, ex.Message);
            }
        }

        public Tuple<bool, string> CreateDataTableSQL(string dt, List<DatatableDTO> dto)
        {
            string content = "";
            foreach (var item in dto)
            {
                try
                {
                    //TODO: 待优化
                    string name = item.Name;
                    string type = item.Type;
                    string required = item.Required == true ? "not null " : " ";

                    var result = DataTypeHelper.ConstraintStatus(item.Constraint, dto);
                    if (!result.Success)
                    {
                        MessageBox.Show(result.Content, "提示", MessageBoxButton.OK, MessageBoxImage.Error);
                        return Tuple.Create(false, result.Content);
                    }
                    string constraint = result.Content;
                    string defaults = string.IsNullOrWhiteSpace(item.Default) ? "" : DataTypeHelper.IsCharType(item.Type.Trim()) == true ? $" default('{item.Default}')" : $" default({item.Default})";

                    content += name + " " + type + " " + required + constraint + defaults + ",";

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "提示", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }
            var info = AnalyzeConnectionString(DBType.SqlServer);

            return Tuple.Create(true, $"use {info.Item2} create table {dt} ({content})");
        }

        public Tuple<bool, string> IsTableExist(string table)
        {
            string isTableStr = $"select OBJECT_ID(N'{table}',N'U')";
            var dt = DBHelper.ExecuteQuery(isTableStr, CommandType.Text);
            if (!string.IsNullOrEmpty(dt.Rows[0][0].ToString()))
            {
                return Tuple.Create(true, string.Empty);
            }
            return Tuple.Create(false, "数据表已存在");
        }

        public Tuple<bool, string> AnalyzeConnectionString(DBType dBType)
        {
            return Tuple.Create(true, string.Empty);
        }
    }
}
